package com.jacen.fzxs.util;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * Excel工具类
 * 兼容2003和2007
 * @author Administrator
 * 
 */
public class ExcelUtils {

	/**
	 * 导入
	 * 
	 * @param excelFile
	 *            excel文件
	 * @param sheetIndex
	 *            工作表的索引 (起始索引从 0 开始)
	 * @param startRowNum
	 *            从第几行开始读取(起始行数从1 开始)
	 * @return List
	 * @throws Exception
	 */
	public static List<String[]> excelToData(File excelFile, int sheetIndex,
			int startRowNum) throws Exception {
		List<String[]> dataList = new ArrayList<String[]>();
		Workbook wbs = WorkbookFactory.create(excelFile);
		Sheet sheet = wbs.getSheetAt(sheetIndex);
		if (sheet == null) return dataList;
		int lastRowNum = sheet.getLastRowNum();
		if (lastRowNum < 0) return dataList;
		for (int i = startRowNum; i <= lastRowNum; i++) {
			String[] columnDatas = ExcelUtils.getRow(sheet, i);
			if (columnDatas == null || columnDatas.length == 0)
				continue;
			dataList.add(columnDatas);
		}
		return dataList;
	}

	/**
	 * 指定工作表和行数的内容
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @return yanzihui 2013-12-13 下午3:11:22
	 */
	public static String[] getRow(Sheet sheet, int rowIndex) {
		if (sheet == null)
			return null;
		String[] rowData = null;
		Row row = sheet.getRow(rowIndex);
		if (isBlankRow(row))
			return new String[] {};

		int cellCount = 17;// cailiangjie xiugai
		rowData = new String[cellCount + 1];
		for (int colIndex = 0; colIndex <= cellCount; colIndex++) {
			String cellValue = getCellValue(sheet, rowIndex, colIndex);
			if (cellValue != null)
				cellValue = cellValue.trim();
			rowData[colIndex] = cellValue;
		}

		return rowData;
	}

	/**
	 * 指定工作表指定行列下的内容
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @param colIndex
	 * @return String
	 */
	public static String getCellValue(Sheet sheet, int rowIndex, int colIndex) {
		if (sheet == null || rowIndex < 0)
			return "";
		String cellValue = "";
		Row row = sheet.getRow(rowIndex);
		Cell cell = row.getCell(colIndex);
		if (cell != null) {
			// 判断cell的类型并统一转换为string类型
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_FORMULA:
				cellValue = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					cellValue = DateUtils.date2Str(cell.getDateCellValue(),
							DateUtils.SIMPLE_FORMAT);
				} else {
					cell.setCellType(Cell.CELL_TYPE_STRING);
					cellValue = cell.getStringCellValue();
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				cellValue = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				cellValue = "";
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				cellValue = String.valueOf(cell);
				break;
			default:
				cellValue = "";
				break;
			}
		}
		return cellValue;
	}

	
	/**
	 * 是否空白行
	 * 
	 * @param row
	 * @return boolean
	 */
	public static boolean isBlankRow(Row row) {
		if (row == null)
			return true;
		boolean result = true;
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
			String value = "";
			if (cell != null) {
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					value = String.valueOf((int) cell.getNumericCellValue());
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					value = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_FORMULA:
					value = String.valueOf(cell.getCellFormula());
					break;
				default:
					break;
				}
				if (!value.trim().equals("")) {
					result = false;
					break;
				}
			}
		}

		return result;
	}

	

}
